mybatis oracle BLOB类型字段保存与读取
一、BLOB字段
BLOB是指二进制大对象也就是英文Binary Large Object的所写，而CLOB是指大字符对象也就是英文Character Large Object的所写。其中BLOB是用来存储大量二进制数据的；CLOB用来存储大量文本数据。BLOB通常用来保存图片、文件等二进制类型的数据。
二、使用mybatis操作blob
1、表结构如下：
create table BLOB_FIELD ( ID VARCHAR2(64 BYTE) not null, TAB_NAME VARCHAR2(64 BYTE) not null, TAB_PKID_VALUE VARCHAR2(64 BYTE) not null, CLOB_COL_NAME VARCHAR2(64 BYTE) not null, CLOB_COL_VALUE CLOB, constraint PK_BLOB_FIELD primary key (ID) );
2、实体代码如下：
1 package com.test.entity; 2 3 import java.sql.Clob; 4 5 /** 6 * 大字段 7 */ 8 public class BlobField { 9 10 private String tabName;// 表名 11 private String tabPkidValue;// 主键值 12 private String blobColName;// 列名 13 private byte[] blobColValue;// 列值 clob类型 14 15 public String getTabName() { 16 return tabName; 17 } 18 19 public void setTabName(String tabName) { 20 this.tabName = tabName; 21 } 22 23 public String getTabPkidValue() { 24 return tabPkidValue; 25 } 26 27 public void setTabPkidValue(String tabPkidValue) { 28 this.tabPkidValue = tabPkidValue; 29 } 30 31 public String getBlobColName() { 32 return blobColName; 33 } 34 35 public void setBlobColName(String blobColName) { 36 this.blobColName = blobColName; 37 } 38 39 public byte[] getBlobColValue() { 40 return blobColValue; 41 } 42 43 public void setBlobColValue(byte[] blobColValue) { 44 this.blobColValue = blobColValue; 45 } 46 47 }
3、mybatis sql代码如下：
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.test.dao.BlobFieldDao"> 4 5 <sql id="blobFieldColumns"> 6 a.ID AS id, 7 a.TAB_NAME AS tabName, 8 a.TAB_PKID_VALUE AS tabPkidValue, 9 a.BLOB_COL_NAME AS blobColName, 10 a.BLOB_COL_VALUE AS blobColValue 11 </sql> 12 13 <sql id="blobFieldJoins"> 14 </sql> 15 16 <select id="get" resultType="blobField"> 17 SELECT 18 <include refid="blobFieldColumns" /> 19 FROM BLOB_FIELD a 20 <include refid="blobFieldJoins" /> 21 WHERE a.ID = #{id} 22 </select> 23 24 <select id="findList" resultType="blobField"> 25 SELECT 26 <include refid="blobFieldColumns" /> 27 FROM BLOB_FIELD a 28 <include refid="blobFieldJoins" /> 29 </select> 30 31 <insert id="insert"> 32 INSERT INTO BLOB_FIELD( 33 ID , 34 TAB_NAME , 35 TAB_PKID_VALUE , 36 BLOB_COL_NAME , 37 BLOB_COL_VALUE 38 ) VALUES ( 39 #{id}, 40 #{tabName}, 41 #{tabPkidValue}, 42 #{blobColName}, 43 #{blobColValue,jdbcType=BLOB} 44 ) 45 </insert> 46 47 <update id="update"> 48 UPDATE BLOB_FIELD SET 49 TAB_NAME = #{tabName}, 50 TAB_PKID_VALUE = #{tabPkidValue}, 51 BLOB_COL_NAME = #{blobColName}, 52 BLOB_COL_VALUE = #{blobColValue} 53 WHERE ID = #{id} 54 </update> 55 <delete id="delete"> 56 DELETE FROM BLOB_FIELD 57 WHERE ID = #{id} 58 </delete> 59 60 </mapper>
3、controller代码如下：
a、保存BLOB字段代码
1 /** 2 * 附件上传 3 * 4 * @param testId 5 * 主表Id 6 * @param request 7 * @return 8 * @throws UnsupportedEncodingException 9 */ 10 @RequiresPermissions("exc:exceptioninfo:feedback") 11 @RequestMapping(value = "attachment", method = RequestMethod.POST) 12 @ResponseBody 13 public Map<String, Object> uploadAttachment(@RequestParam(value = "testId", required = true) String testId, 14 15 HttpServletRequest request) 16 throws UnsupportedEncodingException { 17 Map<String, Object> result = new HashMap<String, Object>(); 18 19 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; 20 // 获得文件 21 MultipartFile multipartFile = multipartRequest.getFile("Filedata");// 与前端设置的fileDataName属性值一致 22 String filename = multipartFile.getOriginalFilename();// 文件名称 23 InputStream is = null; 24 try { 25 //读取文件流 26 is = multipartFile.getInputStream(); 27 byte[] bytes = FileCopyUtils.copyToByteArray(is); 28 BlobField blobField = new BlobField(); 29 blobField.setTabName("testL"); 30 blobField.setTabPkidValue(testId); 31 blobField.setBlobColName("attachment"); 32 blobField.setBlobColValue(bytes); 33 //保存blob字段 34 this.testService.save(blobField, testId, filename); 35 result.put("flag", true); 36 result.put("attachmentId", blobField.getId()); 37 result.put("attachmentName", filename); 38 } catch (IOException e) { 39 e.printStackTrace(); 40 result.put("flag", false); 41 } finally { 42 IOUtils.closeQuietly(is); 43 } 44 return result; 45 }
b、读取BLOB字段
1 /** 2 * 下载附件 3 * 4 * @param attachmentId 5 * @return 6 */ 7 @RequiresPermissions("exc:exceptioninfo:view") 8 @RequestMapping(value = "download", method = RequestMethod.GET) 9 public void download(@RequestParam(value = "attachmentId", required = true) String attachmentId, 10 @RequestParam(value = "attachmentName", required = true) String attachmentName, HttpServletRequest 11 12 request, HttpServletResponse response) { 13 ServletOutputStream out = null; 14 try { 15 response.reset(); 16 String userAgent = request.getHeader("User-Agent"); 17 byte[] bytes = userAgent.contains("MSIE") ? attachmentName.getBytes() : attachmentName.getBytes("UTF- 18 19 8"); // fileName.getBytes("UTF-8")处理safari的乱码问题 20 String fileName = new String(bytes, "ISO-8859-1"); 21 // 设置输出的格式 22 response.setContentType("multipart/form-data"); 23 response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(attachmentName, 24 25 "UTF-8")); 26 BlobField blobField = this.blobFieldService.get(attachmentId); 27 //获取blob字段 28 byte[] contents = blobField.getBlobColValue(); 29 out = response.getOutputStream(); 30 //写到输出流 31 out.write(contents); 32 out.flush(); 33 } catch (IOException e) { 34 e.printStackTrace(); 35 } 36 }
本例子将文件上传并保存到BLOB类型字段字段，下载的时候读取BLOB字段，并写入成输出流。
